-- -------------------------------------------------
--    Project Name:  平台中断-所有明细数据
--    File Name:     jms_dm.dm_cn_breakoff_detail_total_dt
--    Description:   计算（桃花岛、紫金山、逍遥峰）中心-中心、中心-集散、中心-网点超过24、48、72、96、120小时数据
--    Author:        Joyce | 利玉仙
--    date:          2021-12-02
-- -------------------------------------------------
with t_waybill as (--运单取目的网点及客户名称
    select *
    from (
             select waybill_no
                  , customer_code
                  , customer_name
                  , dispatch_network_code
                  , dispatch_network_name
                  , sign_network_code
                  , sign_network_name
                  , row_number() over (partition by waybill_no order by last_update_time desc, sign_time desc, input_time desc) as rnk
             from jms_ods.yl_oms_oms_waybill
             where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -20) and '{{ execution_date | date_add(-1) | cst_ds }}'
         ) a
    where a.rnk = 1
),
     t_order as (--多平台订单来源
         select waybill_no          as waybill_id
              , case
                    when ordersource_code = 'D67' then 2 --抖音'紫金山'
                    when ordersource_code = 'D190' then 3 --快手'逍遥峰'
                    when ordersource_code in ('D09') then 1 --桃花岛
                    else 4 end    as order_source_code
              , case
                    when ordersource_code = 'D67' then '紫金山'
                    when ordersource_code = 'D190' then '逍遥峰'
                    when ordersource_code in ('D09') then '桃花岛'
                    else '其他' end as order_source_name
              , input_time        as read_time
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt BETWEEN date_add('{{ execution_date | date_add(-1) | cst_ds }}', -20) and '{{ execution_date | date_add(-1) | cst_ds }}'
     ),
     t_union_shipment as (--支线干线经停点
         select shipment_no,
                sort_num,
                network_code,
                network_name,
                planned_arrival_time,
                planned_departure_time,
                actual_arrival_time,
                actual_departure_time,
                lst_network_code,
                lst_network_name,
                nst_network_code,
                nst_network_name,
                case
                    when a.lst_network_code is not null
                        and a.nst_network_code is not null then 1 --经停点
                    else 2 --非经停点
                    end as stop_type
         from (
                  select shipment_no,
                         sort_num,
                         network_code,
                         network_name,
                         planned_arrival_time,
                         planned_departure_time,
                         actual_arrival_time,
                         actual_departure_time,
                         lag(network_code) over w  as lst_network_code,--上一站
                         lag(network_name) over w  as lst_network_name,
                         lead(network_code) over w as nst_network_code,--下一站
                         lead(network_name) over w as nst_network_name
                  from (
                           select shipment_no,
                                  sort_num,
                                  network_code,
                                  network_name,
                                  planned_arrival_time,
                                  planned_departure_time,
                                  actual_arrival_time,
                                  actual_departure_time
                           from jms_ods.yl_tmsnew_tms_shipment_stop
                           where dt = date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
                           union all
                           select shipment_no,
                                  sort_num,
                                  network_code,
                                  network_name,
                                  planned_arrival_time,
                                  planned_departure_time,
                                  actual_arrival_time,
                                  actual_departure_time
                           from jms_ods.yl_tmsnew_branch_shipment_stop
                           where dt = date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
                       ) a
                  group by shipment_no,
                           sort_num,
                           network_code,
                           network_name,
                           planned_arrival_time,
                           planned_departure_time,
                           actual_arrival_time,
                           actual_departure_time
                      window w as (partition by shipment_no order by sort_num)
              ) a
     ),
--      t_loading as (
--          select *
--          from (
--                   select billcode
--                        , scansitecode
--                        , nextstationcode
--                        , transfercode
--                        , row_number() over (partition by billcode,scansitecode,nextstationcode order by scantime desc) rnk
--                   from jms_ods.tab_barscan_loading
--                   where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
--               ) a
--          where a.rnk = 1
--      ),
--      t_unloading as (
--          select *
--          from (
--                   select billcode
--                        , scansitecode
--                        , nextstationcode
--                        , transfercode
--                        , row_number() over (partition by billcode,scansitecode,nextstationcode order by scantime asc) rnk
--                   from jms_ods.tab_barscan_unloading
--                   where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
--               ) a
--          where a.rnk = 1
--      ),
     t_union as (
         select billcode
              , scansitecode
              , ''           as nextstationcode
              , scantime
              , 'unloading'  as scantype
              , ''           as send_shipment
              , transfercode as arrival_shipment
              , ''           as arrival_time
              , ''           as send_time
         from jms_ods.tab_barscan_unloading
         where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
           and billcode like 'JT%'
         union all
         select billcode
              , scansitecode
              , nextstationcode
              , scantime
              , 'loading'    as scantype
              , transfercode as send_shipment
              , ''           as arrival_shipment
              , ''           as arrival_time
              , ''           as send_time
         from jms_ods.tab_barscan_loading
         where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
           and billcode like 'JT%'
         union all
         select billcode
              , scansitecode
              , ''                       as nextstationcode
              , scantime
              , 'arrival'                as scantype
              , ''                       as send_shipment
              , transfercode             as arrival_shipment
              , cast(scantime as string) as arrival_time
              , ''                       as send_time
         from jms_ods.tab_barscan_arrival
         where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
           and billcode like 'JT%'
         union all
         select billcode
              , scansitecode
              , nextstationcode
              , scantime
              , 'send'                   as scantype
              , transfercode             as send_shipment
              , ''                       as arrival_shipment
              , ''                       as arrival_time
              , cast(scantime as string) as send_time
         from jms_ods.tab_barscan_send
         where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
           and billcode like 'JT%'
         union all
         select billcode
              , scansitecode
              , ''        as nextstationcode
              , scantime
              , 'bagging' as scantype
              , ''        as send_shipment
              , ''        as arrival_shipment
              , ''        as arrival_time
              , ''        as send_time
         from jms_ods.tab_barscan_bagging
         where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
           and billcode like 'JT%'
         union all
         select billcode
              , scansitecode
              , ''          as nextstationcode
              , scantime
              , 'unbagging' as scantype
              , ''          as send_shipment
              , ''          as arrival_shipment
              , ''          as arrival_time
              , ''          as send_time
         from jms_ods.tab_barscan_other
         where scantype = '拆包扫描'
           and dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
           and billcode like 'JT%'
     ),
     t_lag_union as (--根据扫描时间排序得到运单所经站点序号
         select billcode
              , scansitecode
              , nextstationcode
              , scantime
              , scantype
              , send_shipment
              , arrival_shipment
              , sum(if(nvl(scansitecode != lag(scansitecode) OVER w, TRUE), 1, 0)) OVER w AS step
         from t_union t_union
             window w as (partition by billcode order by scantime)
     ),
     t_dis_union as (--根据运单及站点序号去重取最早最晚操作类型及时间
         select billcode                                                                        as billcode--运单号
              , step                                                                            as step
              , max(scansitecode)                                                               as scan_code--机构编码
              , sum(if(scantype = 'arrival', 1, 0))                                             AS arrival_cnt
              , min(if(scantype = 'arrival', scantime, NULL))                                   AS est_arrival_time
              , max(if(scantype = 'arrival', scantime, NULL))                                   AS lst_arrival_time
              , sum(if(scantype = 'send', 1, 0))                                                AS send_cnt
              , min(if(scantype = 'send', scantime, NULL))                                      AS est_send_time
              , max(if(scantype = 'send', scantime, NULL))                                      AS lst_send_time
              , count(scantype)                                                                 AS scan_cnt
              , min(scantime)                                                                   AS est_scantime
              , min(array(scantime, scantype))[1]                                               AS est_scantype
              , max(scantime)                                                                   AS lst_scantime
              , max(array(scantime, scantype))[1]                                               AS lst_scantype
              , max(if(scantype = 'send' or scantype = 'loading', send_shipment, null))         as send_shipment
              , min(if(scantype = 'arrival' or scantype = 'unloading', arrival_shipment, null)) as arrival_shipment
              , max(nextstationcode)                                                            as nextstationcode
         from t_lag_union t_lag_union
         group by billcode, step),
     t_step as (--根据运单及站点排序得出下一站记录
         SELECT *
              , lead(scan_code) OVER odr_w        AS next_scan_code
              , lead(est_scantime) OVER odr_w     AS next_est_scantime
              , lead(est_scantype) OVER odr_w     AS next_est_scantype
              , lead(lst_scantime) OVER odr_w     AS next_lst_scantime
              , lead(lst_scantype) OVER odr_w     AS next_lst_scantype
              , lead(send_shipment) OVER odr_w    AS next_send_shipment
              , lead(arrival_shipment) OVER odr_w AS next_arrival_shipment
         FROM t_dis_union t_dis_union
             WINDOW odr_w AS (PARTITION BY t_dis_union.billcode ORDER BY t_dis_union.step)
     )
        ,
     t_ship_step as (
         select t_step.billcode--运单号
              , t_step.step
              , t_step.scan_code--机构编码
              , t_step.arrival_cnt
              , t_step.est_arrival_time
              , t_step.lst_arrival_time
              , t_step.send_cnt
              , t_step.est_send_time
              , t_step.lst_send_time
              , t_step.scan_cnt
              , t_step.est_scantime
              , t_step.est_scantype
              , t_step.lst_scantime
              , t_step.lst_scantype
              , t_step.nextstationcode
              , t_step.next_scan_code
              , t_step.next_est_scantime
              , t_step.next_est_scantype
              , t_step.next_lst_scantime
              , t_step.next_lst_scantype
              , coalesce(send_shipment, next_arrival_shipment) as send_shipment--发件任务号，优先发件任务号，顺延下一站到件任务号
              , coalesce(next_arrival_shipment, send_shipment) as next_arrival_shipment--下一站到件任务号
         from t_step t_step
     ),
     t_add_time as (
         select billcode                                                                as billcode             --运单
              , scan_code                                                               as scan_code--扫描机构
              , (unix_timestamp(lst_scantime) - unix_timestamp(est_scantime)) / 60      as breakoff_min--中断时长
              , 0                                                                       as breakoff_type--中断类型：0机构内，1机构间
              , arrival_cnt                                                             as arrival_cnt--到件次数
              , send_cnt                                                                as send_cnt--发件次数
              , (unix_timestamp(lst_send_time) - unix_timestamp(est_arrival_time)) / 60 as stop_min--机构停留时长（min）
              , est_scantime                                                            as breakoff_scan_time--中断扫描时间 机构内本机构最早扫描时间
              , est_scantype                                                            as breakoff_scan_type--中断扫描类型
              , scan_code                                                               as next_scan_code--下一环节扫描网点
              , lst_scantime                                                            as next_est_scantime--下一环节扫描时间
              , lst_scantype                                                            as next_est_scantype--下一环节扫描类型
              , null                                                                    as send_shipment--发车任务号
              , null                                                                    as next_arrival_shipment--下一站到车任务号
         from t_ship_step t_step --机构内
         union all
         select billcode                                                                as billcode             --运单
              , scan_code                                                               as scan_code--扫描机构
              , (unix_timestamp(next_est_scantime) - unix_timestamp(lst_scantime)) / 60 as breakoff_min--中断时长
              , 1                                                                       as breakoff_type--中断类型：0机构内，1机构间
              , arrival_cnt                                                             as arrival_cnt--到件次数
              , send_cnt                                                                as send_cnt--发件次数
              , (unix_timestamp(lst_send_time) - unix_timestamp(est_arrival_time)) / 60 as stop_min--机构停留时长（min）
              , lst_scantime                                                            as breakoff_scan_time--中断扫描时间 机构间本机构最晚扫描时间
              , lst_scantype                                                            as breakoff_scan_type--中断扫描类型 机构间本机构最晚扫描类型
              , next_scan_code                                                          as next_scan_code--下一环节扫描网点
              , next_est_scantime                                                       as next_est_scantime--下一环节扫描时间
              , next_est_scantype                                                       as next_est_scantype--下一环节扫描类型
              , send_shipment                                                           as send_shipment--发车任务号
              , next_arrival_shipment                                                   as next_arrival_shipment--下一站到车任务号
         from t_ship_step t_step--机构间
     )
insert
overwrite
table
jms_dm.dm_cn_breakoff_detail_total_dt
partition
(
dt
)
select billcode
     , taking_time
     , read_time
     , order_source_code
     , order_source_name
     , network_code
     , network_name
     , network_type
     , agent_code
     , agent_name
     , franchisee_code
     , franchisee_name
     , arrival_cnt
     , send_cnt
     , send_shipment
     , stop_min
     , stop_over_12
     , breakoff_scan_time
     , breakoff_min
     , breakoff_type
     , breakoff_scan_type
     , next_scan_code
     , next_scan_name
     , next_scan_type
     , next_agent_code
     , next_agent_name
     , next_franchisee_code
     , next_franchisee_name
     , next_est_scantime
     , next_est_scantype
     , actual_departure_time
     , planned_departure_time
     , actual_arrival_time
     , planned_arrival_time
     , actual_trans_min
     , planned_trans_min
     , delay_trans_min
     , end_code
     , end_name
     , customer_code
     , customer_name
     , mage_region_code
     , mage_region_name
     , dt
from (
         select t_add_time.billcode                                                                                                                                                                          --运单
              , null                                                                                                                                                                as taking_time           --揽收时间
              , null                                                                                                                                                                as read_time--查询时间
              , t_order.order_source_code                                                                                                                                           as order_source_code--订单来源
              , t_order.order_source_name                                                                                                                                           as order_source_name--订单来源
              , t_add_time.scan_code                                                                                                                                                as network_code--机构
              , t_network.name                                                                                                                                                      as network_name
              , case
                    when net.type_id = 335 then 1
                    when net.type_id = 336 AND net.is_entrepot = 1 then 2
                    else 3 end                                                                                                                                                      as network_type
              , t_network.subordinate_agent_code                                                                                                                                    as agent_code            --机构代理区
              , t_network.subordinate_agent_name                                                                                                                                    as agent_name
              , coalesce(t_network.first_franchisee_code, t_network.second_franchisee_code)                                                                                         as franchisee_code--机构加盟商
              , coalesce(t_network.first_franchisee_name, t_network.second_franchisee_name)                                                                                         as franchisee_name
              , t_add_time.arrival_cnt                                                                                                                                              as arrival_cnt--到件次数
              , t_add_time.send_cnt                                                                                                                                                 as send_cnt--发件次数
              , t_add_time.send_shipment                                                                                                                                            as send_shipment--发件任务号
              , t_add_time.stop_min                                                                                                                                                 as stop_min
              , case
                    when t_add_time.arrival_cnt = 0 and t_add_time.send_cnt = 0 then 1 --到件、发件漏扫
                    when t_add_time.arrival_cnt = 0 and t_add_time.send_cnt != 0 then 2--到件漏扫
                    when t_add_time.arrival_cnt != 0 and t_add_time.send_cnt = 0 then 3--发件漏扫
                    when t_add_time.arrival_cnt != 0 and t_add_time.send_cnt != 0 and stop_min > 12 * 60 then 4--超12小时
                    when t_add_time.arrival_cnt != 0 and t_add_time.send_cnt != 0 and stop_min < -12 * 60 then 5--超12小时（负）
                    when t_add_time.arrival_cnt != 0 and t_add_time.send_cnt != 0 and abs(stop_min) <= 12 * 60 then 6--未超12小时
                    else 7 end                                                                                                                                                      as stop_over_12--责任机构到件和发件时间间隔超12小时
              , t_add_time.breakoff_scan_time                                                                                                                                       as breakoff_scan_time--中断机构扫描时间
              , t_add_time.breakoff_min                                                                                                                                             as breakoff_min--中断时长
              , t_add_time.breakoff_type                                                                                                                                            as breakoff_type--0机构内，1机
              , t_add_time.breakoff_scan_type                                                                                                                                       as breakoff_scan_type--中断扫描类型
              , t_add_time.next_scan_code                                                                                                                                           as next_scan_code--下一操作网点
              , t_next_network.name                                                                                                                                                 as next_scan_name
              , case
                    when next_net.type_id = 335 then 1
                    when next_net.type_id = 336 AND next_net.is_entrepot = 1 then 2
                    else 3 end                                                                                                                                                      as next_scan_type
              , t_next_network.subordinate_agent_code                                                                                                                               as next_agent_code
              , t_next_network.subordinate_agent_name                                                                                                                               as next_agent_name
              , coalesce(t_next_network.first_franchisee_code, t_next_network.second_franchisee_code)                                                                               as next_franchisee_code
              , coalesce(t_next_network.first_franchisee_name, t_next_network.second_franchisee_name)                                                                               as next_franchisee_name
              , t_add_time.next_est_scantime                                                                                                                                        as next_est_scantime--下一操作网点扫描时间
              , t_add_time.next_est_scantype                                                                                                                                        as next_est_scantype--下一操作网点扫描类型
              , t_send_shipment.actual_departure_time                                                                                                                               as actual_departure_time--实际发车时间
              , t_send_shipment.planned_departure_time                                                                                                                              as planned_departure_time--规划发车时间
              , t_arrival_shipment.actual_arrival_time                                                                                                                              as actual_arrival_time--下一站实际到车时间
              , t_arrival_shipment.planned_arrival_time                                                                                                                             as planned_arrival_time--下一站规划到车时间
              , (unix_timestamp(t_arrival_shipment.actual_arrival_time) - unix_timestamp(t_send_shipment.actual_departure_time)) /
                60                                                                                                                                                                  as actual_trans_min--实际运输时长
              , (unix_timestamp(t_arrival_shipment.planned_arrival_time) - unix_timestamp(t_send_shipment.planned_departure_time)) /
                60                                                                                                                                                                  as planned_trans_min--规划运输时长
              , ((unix_timestamp(t_arrival_shipment.planned_arrival_time) - unix_timestamp(t_send_shipment.planned_departure_time)) -
                 (unix_timestamp(t_arrival_shipment.actual_arrival_time) - unix_timestamp(t_send_shipment.actual_departure_time))) /
                60                                                                                                                                                                  as delay_trans_min--运输延误时间
              , nvl(waybill.sign_network_code, waybill.dispatch_network_code)                                                                                                       as end_code--目的网点
              , nvl(waybill.sign_network_name, waybill.dispatch_network_name)                                                                                                       as end_name
              , waybill.customer_code--客户编码
              , waybill.customer_name
              , t_region.name                                                                                                                                                       as mage_region_name      --管理大区
              , t_region.code                                                                                                                                                       as mage_region_code
              , row_number() over (partition by billcode,scan_code,next_scan_code order by t_arrival_shipment.actual_arrival_time desc, t_send_shipment.actual_departure_time desc) as rnk
              , '{{ execution_date | date_add(-1) | cst_ds }}'                                                                                                                      as dt
         from t_add_time t_add_time
                  left join t_order t_order --订单来源
                            on t_add_time.billcode = t_order.waybill_id
                  left join jms_dim.dim_lmdm_sys_network_expand AS t_network
                            ON t_add_time.scan_code = t_network.code
                  left join jms_dim.dim_lmdm_sys_network_expand AS t_next_network
                            ON t_add_time.next_scan_code = t_next_network.code
                  left join jms_dim.dim_lmdm_sys_network net--网点类型
                            on t_add_time.scan_code = net.code
                  left join jms_dim.dim_lmdm_sys_network next_net
                            on t_add_time.next_scan_code = next_net.code
                  left join t_union_shipment t_send_shipment--发车任务
                            on t_add_time.send_shipment = t_send_shipment.shipment_no
                                and t_add_time.scan_code = t_send_shipment.network_code
                  left join t_union_shipment t_arrival_shipment--下一站到车任务
                            on t_add_time.send_shipment = t_arrival_shipment.shipment_no
                                and t_add_time.next_scan_code = t_arrival_shipment.network_code
                  LEFT JOIN t_waybill waybill
                            on t_add_time.billcode = waybill.waybill_no
                  left join jms_ods.sys_manage_region t_region
                            on t_region.dt = date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
                                and t_region.proxy_network_id = t_network.subordinate_agent_id
         WHERE t_add_time.breakoff_min >= 24 * 60 -- 扫描间隔超过 24 小时视为中断
           and net.type_id = 335--只统计中心-中心或者中心-集散/网点数据
     ) a
         left anti join jms_ods.tab_reback_transfer_express t_reback
                        on a.billcode = t_reback.waybill_no
                            and t_reback.dt = date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
                            and t_reback.apply_type_code = 4--退回件
                            and a.breakoff_scan_time >= t_reback.apply_time--剔除因退回件导致的中断数据
where a.rnk = 1
    DISTRIBUTE BY dt
    , pmod(hash(billcode), 80);
